﻿using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Xml.Linq;

namespace LPBToolsLib
{
    /*
    AppSetting.json格式

    "SQLSerCFG": {
		"Address": "192.168.0.3",
		"DBUser": "MESProxy",
		"DBPass": "MESProxy",
		"DBName": "LDSys"
	},
     
    修改20240724：
    1、对DBPass进行AESSelf加密处理
     */
    public interface IFSQLConfig
    {
        [DisplayName("服务地址")]
        string Address { get;  }
        [DisplayName("登陆用户")]
        string DBUser { get; }
        [DisplayName("登陆密码")]
        string DBPass { get;  }
        [DisplayName("连接初始数据库名称")]
        string DBName { get; }
        [DisplayName("连接端口")]
        int DBPort { get; }
    }

    public class SQLSerConfigINI : IFSQLConfig
    {
        protected JSONINISettings BaseSetCFG { get; set; } = null;
        public string Segment { get; private set; } = "";
        public SQLSerConfigINI(JSONINISettings tBaseSetCFG, string DestSegment = "")
        {
            BaseSetCFG = tBaseSetCFG;
            if (string.IsNullOrEmpty(DestSegment))
                Segment = "SQLServer";
            else
                Segment = DestSegment;
        }

        [DisplayName("服务地址")]
        public string Address
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<string>(Segment, "Address", "192.168.0.1");
                return "192.168.0.1";
            }
            set { BaseSetCFG?.setCFGValue(Segment, "Address", value); }
        }


        [DisplayName("登陆用户")]
        public string DBUser
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<string>(Segment, "DBUser", "");
                return "";
            }
            set { BaseSetCFG?.setCFGValue(Segment, "DBUser", value); }
        }

        [DisplayName("登陆密码")]
        public string DBPass
        {
            get
            {
                if (BaseSetCFG != null)
                {
                    string sTmp = BaseSetCFG.getCFGValue<string>(Segment, "DBPass", "");
                    if (!string.IsNullOrEmpty(sTmp))
                    {
                        try
                        {
                            sTmp = SecurityTools.AESSelfDecrypt(sTmp);
                        }
                        catch
                        {
                            sTmp = "";
                        }
                    }
                    return sTmp;
                }
                return "";
            }
            set { BaseSetCFG?.setCFGValue(Segment, "DBPass", SecurityTools.AESSelfEncrypt(value) ); }
        }

        [DisplayName("连接初始数据库名称")]
        public string DBName
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<string>(Segment, "DBName", "");
                return "";
            }
            set { BaseSetCFG?.setCFGValue(Segment, "DBName", value); }
        }

        [DisplayName("连接端口")]
        public int DBPort
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<int>(Segment, "DBPort", 1433);
                return 1433;
            }
            set { BaseSetCFG?.setCFGValue(Segment, "DBPort", value); }
        }

        [DisplayName("SQLSer连接字符串[SqlConnection]")]
        public virtual string ConnectStr
        {
            get
            {
                string sMask = "Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}";// ;TrustServerCertificate = true; MultipleActiveResultSets=true; 
                return string.Format(sMask,
                    Address, DBName, DBUser, DBPass);
            }
        }
    }

    public class SQLSerConfigReg : IFSQLConfig
    {
        protected const string subBaseKey = @"Software\LPBStudio\";

        protected string sGroupName { get; set; } = "AppConfig";

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="sDestGroupName">App具体分组名称</param>
        public SQLSerConfigReg(string sDestGroupName)
        {
            if (!string.IsNullOrEmpty(sDestGroupName))
                sGroupName = sDestGroupName;
        }

        protected string ReadStringValue(string valueName, string defaultValue = "")
        {
            string subKey = subBaseKey + sGroupName;
            try
            {
                using (RegistryKey key = Registry.CurrentUser.OpenSubKey(subKey))
                {
                    return key?.GetValue(valueName)?.ToString() ?? defaultValue;
                }
            }
            catch (Exception)
            {
                return defaultValue;
            }
        }

        protected bool WriteStringValue(string valueName, string value)
        {
            string subKey = subBaseKey + sGroupName;
            try
            {
                using (RegistryKey key = Registry.CurrentUser.CreateSubKey(subKey))
                {
                    key.SetValue(valueName, value, RegistryValueKind.String);
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

        [DisplayName("SQLSer服务地址")]
        public string Address
        {
            get { return ReadStringValue("Address", "192.168.0.1"); }
            set { WriteStringValue("Address", value); }
        }

        [DisplayName("SQLSer登陆用户")]
        public string DBUser
        {
            get { return ReadStringValue("DBUser", ""); }
            set { WriteStringValue("DBUser", value); }
        }

        [DisplayName("SQLSer登陆密码")]
        public string DBPass
        {
            get { return ReadStringValue("DBPass", ""); }
            set { WriteStringValue("DBPass", value); }
        }

        [DisplayName("连接初始数据库名称")]
        public string DBName
        {
            get { return ReadStringValue("DBName", ""); }
            set { WriteStringValue("DBName", value); }
        }

        [DisplayName("连接端口")]
        public int DBPort
        {
            get { return int.Parse(ReadStringValue("DBPort", "1433")); }
            set { WriteStringValue("DBPort", value.ToString()); }
        }
    }

    public class SQLSerHelper
    {
        /// <summary>        
        /// 必须使用Encrypt=False;
        /// SSMS(Sql Server Managerment Studio)默认不强制加密（除非明确配置），而 .NET 8 的 Microsoft.Data.SqlClient 默认启用加密（Encrypt=True）
        /// 当使用 IP 地址（如 192.168.1.190）连接时，证书验证会更严格（证书通常是为域名颁发的）
        /// </summary>
        public const string sConnectStrMask = "Server={0};User Id={1};Password={2};Database={3};Encrypt=False;";

        public static bool checkDBConnect(string ServerAddress, string UserName, string Password, string DefaultDBName = "")
        {
            string sSQLConnectStr = string.Format(sConnectStrMask,
                    ServerAddress,
                    UserName,
                    Password,
                    DefaultDBName);
            using (SqlConnection tDBCon = new SqlConnection(sSQLConnectStr))
                try
                {
                    tDBCon.Open();
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
                finally
                {
                    tDBCon.Close();
                }
        }

        public static bool checkDBConnect(IFSQLConfig Config)
        {
            return checkDBConnect(Config.Address,
                        Config.DBUser,
                        Config.DBPass,
                        Config.DBName);
        }
        public string ConnectStr
        {
            get
            {
                return (Config != null) ? string.Format(sConnectStrMask,
                        Config.Address,
                        Config.DBUser,
                        Config.DBPass,
                        Config.DBName) : "";
            }
        }

        public Recorder_Error SysErr { get; set; } = null;
        public IFSQLConfig Config { get; private set; } = null;

        public SQLSerHelper(IFSQLConfig DestConfig)
        {
            Config = DestConfig;
        }
        public SQLSerHelper(IFSQLConfig DestConfig, Recorder_Error DestSysErr)
        {
            Config = DestConfig;
            SysErr = DestSysErr;
        }

        public bool IsSerIPReady
        {
            get { return (Config != null) && BaseTools.PingTest(Config.Address); }
        }

        #region SQL语句执行
        /// <summary>
        /// 执行SQL查询Select语句，同时获得结果数量
        /// </summary>
        /// <param name="DestSql">需要执行的目标SQL，必须包括Select</param>
        /// <param name="recCount">返回结果的数量</param>
        /// <param name="DestSql">需要执行的目标SQL，必须包括Select</param>
        /// <param name="recCount">返回结果的数量</param>
        /// <param name="PageIdx">查询返回数据的起始页，从1开始</param>
        /// <param name="PageCount">查询返回数据每页记录数</param>
        /// <returns>返回的数据接口中包括1个数据结果表</returns>
        public DataTable runSelect(string DestSql, out int recCount,
            int PageIdx = 0, int PageCount = 0)
        {
            recCount = 0;
            DataSet tDataset = new DataSet();
            DataTable tDTable = null;
            if ((!string.IsNullOrEmpty(DestSql)) && (!string.IsNullOrEmpty(ConnectStr))
                && (DestSql.ToLower().IndexOf("select") >= 0))
                try
                {
                    using (SqlConnection tDBCon = new SqlConnection(ConnectStr))
                        try
                        {
                            tDBCon.Open();
                            SqlDataAdapter tAdapter = new SqlDataAdapter();
                            tAdapter.SelectCommand = new SqlCommand(DestSql, tDBCon);
                            string sResultTBName = "SelectResult";
                            if (PageCount > 0)
                            {
                                if (PageIdx <= 0)
                                    PageIdx = 1;
                                // sResultTBName:导入DataSet中的表名称，可以是任何名字，但不能为空
                                tAdapter.Fill(tDataset, (PageIdx - 1) * PageCount, PageCount, sResultTBName);
                            }
                            else
                            {
                                tAdapter.Fill(tDataset, sResultTBName);
                            }
                            tDTable = tDataset.Tables[sResultTBName];
                            recCount = tDTable.Rows.Count;
                        }
                        finally
                        {
                            tDBCon.Close();
                        }
                }
                catch (Exception ex)
                {
                    if (SysErr!=null)
                        SysErr.setRecord(ex);
                    else
                        throw;
                }
            return tDTable;
        }

        /// <summary>
        /// 执行查询，并返回由查询返回的结果集中的第一行的第一列。 其他列或行将被忽略。
        /// </summary>
        /// <param name="DestSql"></param>
        /// <returns>结果集中第一行的第一列或 null 引用（如果结果集为空）</returns>
        public object runSelect(string DestSql)
        {
            string sSQL = DestSql;
            if ((!string.IsNullOrEmpty(DestSql)) && (!string.IsNullOrEmpty(ConnectStr))
                && (DestSql.ToLower().IndexOf("select") >= 0))
                try
                {
                    using (SqlConnection tDBCon = new SqlConnection(ConnectStr))
                        try
                        {
                            tDBCon.Open();
                            SqlCommand tDBCommand = new SqlCommand(DestSql, tDBCon);
                            return tDBCommand.ExecuteScalar();
                        }
                        finally
                        {
                            tDBCon.Close();
                        }
                }
                catch (Exception ex)
                {
                    
                    if (SysErr != null)
                        SysErr.setRecord(ex);
                    else
                        throw;
                }
            return "";
        }

        /// <summary>
        /// Update操作数据，包括Insert操作
        /// </summary>
        /// <param name="updateSQL">等待执行的Update语句</param>
        /// <returns>返回影响的记录数量</returns>
        public int updateValues(string updateSQL)
        {
            if ((!string.IsNullOrEmpty(updateSQL)) && (!string.IsNullOrEmpty(ConnectStr))
                && ((updateSQL.ToLower().IndexOf("update") >= 0) || (updateSQL.ToLower().IndexOf("insert") >= 0)))

                try
                {
                    using (SqlConnection tDBCon = new SqlConnection(ConnectStr))
                        try
                        {
                            tDBCon.Open();
                            SqlCommand tCommand = new SqlCommand(updateSQL, tDBCon);
                            return tCommand.ExecuteNonQuery();
                        }
                        finally
                        {
                            tDBCon.Close();
                        }
                }
                catch (Exception ex)
                {
                    if (SysErr != null)
                        SysErr.setRecord(ex);
                    else
                        throw;
                }
            return -1;
        }

        /// <summary>
        /// 带参数方式写入字段
        /// </summary>
        /// <param name="updateSQL"></param>
        /// <param name="sParameterName">参数名称，不包括@</param>
        /// <param name="sParamaterValue"></param>
        /// <returns></returns>
        public int updateValues(string updateSQL,string sParameterName,string sParamaterValue)
        {
            if ((!string.IsNullOrEmpty(updateSQL)) && (!string.IsNullOrEmpty(ConnectStr))
                && (updateSQL.ToLower().IndexOf("update") >= 0) //  || (updateSQL.ToLower().IndexOf("insert") >= 0)
                && (updateSQL.IndexOf("@"+ sParameterName)>=0))
                try
                {
                    using (SqlConnection tDBCon = new SqlConnection(ConnectStr))
                        try
                        {
                            tDBCon.Open();
                            SqlCommand tCommand = new SqlCommand(updateSQL, tDBCon);
                            tCommand.Parameters.AddWithValue("@"+sParameterName, sParamaterValue);  
                            return tCommand.ExecuteNonQuery();
                        }
                        finally
                        {
                            tDBCon.Close();
                        }
                }
                catch (Exception ex)
                {
                    if (SysErr != null)
                        SysErr.setRecord(ex);
                    else
                        throw;
                }
            return -1;
        }
        #endregion

        public static object runSelectAlone(string DestSql, IFSQLConfig DestConfig)
        {
            SQLSerHelper tSQLRunner = new SQLSerHelper(DestConfig);
            return tSQLRunner.runSelect(DestSql);
        }

        public static DataTable runSelect(string DestSql, out int recCount, IFSQLConfig DestConfig,
            int PageIdx = 0, int PageCount = 0)
        {
            SQLSerHelper tSQLRunner = new SQLSerHelper(DestConfig);
            return tSQLRunner.runSelect(DestSql, out recCount, PageIdx, PageCount);
        }

        public static int updateValuesAlone(string updateSQL, IFSQLConfig DestConfig)
        {
            SQLSerHelper tSQLRunner = new SQLSerHelper(DestConfig);
            return tSQLRunner.updateValues(updateSQL);
        }
    }
}
